Imports System.Data.SqlClient
Imports ConnectionServer
Imports CObject
Imports Objects.Security.Cryptography
''' -----------------------------------------------------------------------------
''' Project	 : Objects
''' Class	 : BusinessInfo
''' 
''' -----------------------------------------------------------------------------
''' <summary>
''' Use this class for obtaining information about our Organizational Structure
''' </summary>
''' <remarks>
''' </remarks>
''' -----------------------------------------------------------------------------
Public Class BusinessInfo
    ''' -----------------------------------------------------------------------------
    ''' Project	 : Objects
    ''' Struct	 : BusinessInfo.Coding
    ''' 
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' A structure returned to provide BU,OU,MU,SMU and ProjectID 
    ''' </summary>
    ''' <remarks>
    ''' </remarks>    
    ''' -----------------------------------------------------------------------------
    Public Structure Coding
        Public BU As String
        Public OpUnit As String
        Public MU As String
        Public SMU As String
        Public CodingBlock As String
        Public ProjectID As String
    End Structure

    Public Function EmployeeSearch(ByVal currentuser As EmployeeInfo.Employee, ByVal psSearchString As String, ByVal psFilter As String) As DataTable
        Dim loConn As ConnectionServer.Connection = New ConnectionServer.Connection(ConnectionServer.Connection.SourceDataBase.CMT)
        Dim loComm As SqlCommand

        psSearchString = psSearchString.Replace(", ", ",")
        psSearchString = psSearchString.Trim

        If psFilter.Trim.Length > 0 Then
            loComm = loConn.Command("EXEC dbo.EmployeeSearch @NetworkID,@SearchString,@Filter")
        Else
            loComm = loConn.Command("EXEC dbo.EmployeeSearch @NetworkID,@SearchString,NULL")
        End If
        Dim loCrypt As Encryption = New Encryption

        Dim lsFilter As String = psFilter

        Dim Crypt As New Objects.Security.Cryptography.Encryption()
        With loComm
            loComm.Parameters.Add(New SqlParameter("@NetworkID", currentuser.NetworkID))
            loComm.Parameters.Add(New SqlParameter("@SearchString", psSearchString.Replace("'", "''")))
            If psFilter.Trim.Length > 0 Then
                loComm.Parameters.Add(New SqlParameter("@Filter", psFilter))
            End If

        End With
        Dim loAdp As SqlDataAdapter = New SqlDataAdapter(loComm)
        Dim loDT As DataTable = New DataTable
        loAdp.Fill(loDT)
        loComm.Connection.Close()
        Return loDT

    End Function

    ''' <summary>
    ''' EmployeeSearchAllEmployees
    ''' </summary>
    ''' <param name="psSearchString"></param>
    ''' <param name="psFilter"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function EmployeeSearchAllEmployees(ByVal psSearchString As String, ByVal psFilter As String) As DataTable
        Using loConn As ConnectionServer.Connection = New ConnectionServer.Connection(ConnectionServer.Connection.SourceDataBase.CMT)
            Dim loComm As SqlCommand

            psSearchString = psSearchString.Replace(", ", ",")
            psSearchString = psSearchString.Trim

            If psFilter.Trim.Length > 0 Then
                loComm = loConn.Command("EXEC dbo.EmployeeSearchAllEmployees @SearchString,@Filter")
            Else
                loComm = loConn.Command("EXEC dbo.EmployeeSearchAllEmployees @SearchString,NULL")
            End If

            Dim loCrypt As Encryption = New Encryption

            Dim lsFilter As String = psFilter

            Dim Crypt As New Objects.Security.Cryptography.Encryption()
            With loComm
                loComm.Parameters.Add(New SqlParameter("@SearchString", psSearchString.Replace("'", "''")))
                If psFilter.Trim.Length > 0 Then
                    loComm.Parameters.Add(New SqlParameter("@Filter", psFilter))
                End If

            End With
            Dim loAdp As SqlDataAdapter = New SqlDataAdapter(loComm)
            Dim loDT As DataTable = New DataTable
            loAdp.Fill(loDT)
            loComm.Connection.Close()
            Return loDT
        End Using
    End Function

    Public Function EmployeeSearchIndiaOnly(ByVal psSearchString As String, ByVal psFilter As String) As DataTable
        Using loConn As ConnectionServer.Connection = New ConnectionServer.Connection(ConnectionServer.Connection.SourceDataBase.CMT)
            Dim loComm As SqlCommand

            psSearchString = psSearchString.Replace(", ", ",")
            psSearchString = psSearchString.Trim

            If psFilter.Trim.Length > 0 Then
                loComm = loConn.Command("EXEC dbo.EmployeeSearchIndiaOnly @SearchString,@Filter")
            Else
                loComm = loConn.Command("EXEC dbo.EmployeeSearchIndiaOnly @SearchString,NULL")
            End If

            Dim loCrypt As Encryption = New Encryption

            Dim lsFilter As String = psFilter

            Dim Crypt As New Objects.Security.Cryptography.Encryption()
            With loComm
                loComm.Parameters.Add(New SqlParameter("@SearchString", psSearchString.Replace("'", "''")))
                If psFilter.Trim.Length > 0 Then
                    loComm.Parameters.Add(New SqlParameter("@Filter", psFilter))
                End If

            End With
            Dim loAdp As SqlDataAdapter = New SqlDataAdapter(loComm)
            Dim loDT As DataTable = New DataTable
            loAdp.Fill(loDT)
            loComm.Connection.Close()
            Return loDT
        End Using
    End Function

    Public Function EmployeeSearchAllEmployeesComplete(ByVal psSearchString As String, ByVal psFilter As String) As DataTable
        Using loConn As ConnectionServer.Connection = New ConnectionServer.Connection(ConnectionServer.Connection.SourceDataBase.CMT)
            Dim loComm As SqlCommand

            psSearchString = psSearchString.Replace(", ", ",")
            psSearchString = psSearchString.Trim

            If psFilter.Trim.Length > 0 Then
                loComm = loConn.Command("EXEC dbo.EmployeeSearchAllEmployeesComplete @SearchString,@Filter")
            Else
                loComm = loConn.Command("EXEC dbo.EmployeeSearchAllEmployeesComplete @SearchString,NULL")
            End If

            Dim loCrypt As Encryption = New Encryption

            Dim lsFilter As String = psFilter

            Dim Crypt As New Objects.Security.Cryptography.Encryption()
            With loComm
                loComm.Parameters.Add(New SqlParameter("@SearchString", psSearchString.Replace("'", "''")))
                If psFilter.Trim.Length > 0 Then
                    loComm.Parameters.Add(New SqlParameter("@Filter", psFilter))
                End If

            End With
            Dim loAdp As SqlDataAdapter = New SqlDataAdapter(loComm)
            Dim loDT As DataTable = New DataTable
            loAdp.Fill(loDT)
            loComm.Connection.Close()
            Return loDT
        End Using
    End Function

    Public Function EmployeeSearchAllEmployees(ByVal psSearchString As String, ByVal psFilter As String, ByVal piMaxResults As Integer) As DataTable
        Using loConn As ConnectionServer.Connection = New ConnectionServer.Connection(ConnectionServer.Connection.SourceDataBase.CMT)
            Dim loComm As SqlCommand

            psSearchString = psSearchString.Replace(", ", ",")
            psSearchString = psSearchString.Trim

            If psFilter.Trim.Length > 0 Then
                loComm = loConn.Command("EXEC dbo.EmployeeSearchAllEmployeesLimit @SearchString,@Filter,@MaxResults")
            Else
                loComm = loConn.Command("EXEC dbo.EmployeeSearchAllEmployeesLimit @SearchString,NULL,@MaxResults")
            End If

            Dim loCrypt As Encryption = New Encryption

            Dim lsFilter As String = psFilter

            Dim Crypt As New Objects.Security.Cryptography.Encryption()
            With loComm
                loComm.Parameters.Add(New SqlParameter("@SearchString", psSearchString.Replace("'", "''")))
                If psFilter.Trim.Length > 0 Then
                    loComm.Parameters.Add(New SqlParameter("@Filter", psFilter))
                End If
                loComm.Parameters.Add(New SqlParameter("@MaxResults", piMaxResults))
            End With
            Dim loAdp As SqlDataAdapter = New SqlDataAdapter(loComm)
            Dim loDT As DataTable = New DataTable
            loAdp.Fill(loDT)
            loComm.Connection.Close()
            Return loDT
        End Using
    End Function

    Public Function EmployeeSearchAllEmployeesComplete(ByVal psSearchString As String, ByVal psFilter As String, ByVal piMaxResults As Integer) As DataTable
        Using loConn As ConnectionServer.Connection = New ConnectionServer.Connection(ConnectionServer.Connection.SourceDataBase.CMT)
            Dim loComm As SqlCommand

            psSearchString = psSearchString.Replace(", ", ",")
            psSearchString = psSearchString.Trim

            If psFilter.Trim.Length > 0 Then
                loComm = loConn.Command("EXEC dbo.EmployeeSearchAllEmployeesLimitComplete @SearchString,@Filter,@MaxResults")
            Else
                loComm = loConn.Command("EXEC dbo.EmployeeSearchAllEmployeesLimitComplete @SearchString,NULL,@MaxResults")
            End If

            Dim loCrypt As Encryption = New Encryption

            Dim lsFilter As String = psFilter

            Dim Crypt As New Objects.Security.Cryptography.Encryption()
            With loComm
                loComm.Parameters.Add(New SqlParameter("@SearchString", psSearchString.Replace("'", "''")))
                If psFilter.Trim.Length > 0 Then
                    loComm.Parameters.Add(New SqlParameter("@Filter", psFilter))
                End If
                loComm.Parameters.Add(New SqlParameter("@MaxResults", piMaxResults))
            End With
            Dim loAdp As SqlDataAdapter = New SqlDataAdapter(loComm)
            Dim loDT As DataTable = New DataTable
            loAdp.Fill(loDT)
            loComm.Connection.Close()
            Return loDT
        End Using
    End Function

    Public Function EmployeeSearchAllEmployeesByProcessingCountry(ByVal psSearchString As String, ByVal psFilter As String, ByVal psProcessingCountry As String) As DataTable
        Using loConn As ConnectionServer.Connection = New ConnectionServer.Connection(ConnectionServer.Connection.SourceDataBase.CMT)
            Dim loComm As SqlCommand

            psSearchString = psSearchString.Replace(", ", ",")
            psSearchString = psSearchString.Trim

            If psFilter.Trim.Length > 0 Then
                loComm = loConn.Command("EXEC dbo.EmployeeSearchAllEmployeesByProcessingCountry @SearchString,@Filter,@ProcessingCountry")
            Else
                loComm = loConn.Command("EXEC dbo.EmployeeSearchAllEmployeesByProcessingCountry @SearchString,NULL,@ProcessingCountry")
            End If

            Dim loCrypt As Encryption = New Encryption

            Dim lsFilter As String = psFilter

            Dim Crypt As New Objects.Security.Cryptography.Encryption()
            With loComm
                loComm.Parameters.Add(New SqlParameter("@SearchString", psSearchString.Replace("'", "''")))
                If psFilter.Trim.Length > 0 Then
                    loComm.Parameters.Add(New SqlParameter("@Filter", psFilter))
                End If
                loComm.Parameters.Add(New SqlParameter("@ProcessingCountry", psProcessingCountry.Replace("'", "''")))

            End With
            Dim loAdp As SqlDataAdapter = New SqlDataAdapter(loComm)
            Dim loDT As DataTable = New DataTable
            loAdp.Fill(loDT)
            loComm.Connection.Close()
            Return loDT
        End Using
    End Function

    Public Function EmployeeSearchAllEmployeesByProcessingCountryComplete(ByVal psSearchString As String, ByVal psFilter As String, ByVal psProcessingCountry As String) As DataTable
        Using loConn As ConnectionServer.Connection = New ConnectionServer.Connection(ConnectionServer.Connection.SourceDataBase.CMT)
            Dim loComm As SqlCommand

            psSearchString = psSearchString.Replace(", ", ",")
            psSearchString = psSearchString.Trim

            If psFilter.Trim.Length > 0 Then
                loComm = loConn.Command("EXEC dbo.EmployeeSearchAllEmployeesByProcessingCountryComplete @SearchString,@Filter,@ProcessingCountry")
            Else
                loComm = loConn.Command("EXEC dbo.EmployeeSearchAllEmployeesByProcessingCountryComplete @SearchString,NULL,@ProcessingCountry")
            End If

            Dim loCrypt As Encryption = New Encryption

            Dim lsFilter As String = psFilter

            Dim Crypt As New Objects.Security.Cryptography.Encryption()
            With loComm
                loComm.Parameters.Add(New SqlParameter("@SearchString", psSearchString.Replace("'", "''")))
                If psFilter.Trim.Length > 0 Then
                    loComm.Parameters.Add(New SqlParameter("@Filter", psFilter))
                End If
                loComm.Parameters.Add(New SqlParameter("@ProcessingCountry", psProcessingCountry.Replace("'", "''")))

            End With
            Dim loAdp As SqlDataAdapter = New SqlDataAdapter(loComm)
            Dim loDT As DataTable = New DataTable
            loAdp.Fill(loDT)
            loComm.Connection.Close()
            Return loDT
        End Using
    End Function

    Public Function EmployeeSearchAllEmployeesByProcessingCountry(ByVal psSearchString As String, ByVal psFilter As String, ByVal piMaxResults As Integer, ByVal psProcessingCountry As String) As DataTable
        Using loConn As ConnectionServer.Connection = New ConnectionServer.Connection(ConnectionServer.Connection.SourceDataBase.CMT)
            Dim loComm As SqlCommand

            psSearchString = psSearchString.Replace(", ", ",")
            psSearchString = psSearchString.Trim

            If psFilter.Trim.Length > 0 Then
                loComm = loConn.Command("EXEC dbo.EmployeeSearchAllEmployeesByProcessingCountry @SearchString,@Filter,@MaxResults,@ProcessingCountry")
            Else
                loComm = loConn.Command("EXEC dbo.EmployeeSearchAllEmployeesByProcessingCountry @SearchString,NULL,@MaxResults,@ProcessingCountry")
            End If

            Dim loCrypt As Encryption = New Encryption

            Dim lsFilter As String = psFilter

            Dim Crypt As New Objects.Security.Cryptography.Encryption()
            With loComm
                loComm.Parameters.Add(New SqlParameter("@SearchString", psSearchString.Replace("'", "''")))
                If psFilter.Trim.Length > 0 Then
                    loComm.Parameters.Add(New SqlParameter("@Filter", psFilter))
                End If
                loComm.Parameters.Add(New SqlParameter("@MaxResults", piMaxResults))
                loComm.Parameters.Add(New SqlParameter("@ProcessingCountry", psProcessingCountry.Replace("'", "''")))

            End With
            Dim loAdp As SqlDataAdapter = New SqlDataAdapter(loComm)
            Dim loDT As DataTable = New DataTable
            loAdp.Fill(loDT)
            loComm.Connection.Close()
            Return loDT
        End Using
    End Function

    Public Function EmployeeSearchAllEmployeesByProcessingCountryComplete(ByVal psSearchString As String, ByVal psFilter As String, ByVal piMaxResults As Integer, ByVal psProcessingCountry As String) As DataTable
        Using loConn As ConnectionServer.Connection = New ConnectionServer.Connection(ConnectionServer.Connection.SourceDataBase.CMT)
            Dim loComm As SqlCommand

            psSearchString = psSearchString.Replace(", ", ",")
            psSearchString = psSearchString.Trim

            If psFilter.Trim.Length > 0 Then
                loComm = loConn.Command("EXEC dbo.EmployeeSearchAllEmployeesByProcessingCountryComplete @SearchString,@Filter,@MaxResults,@ProcessingCountry")
            Else
                loComm = loConn.Command("EXEC dbo.EmployeeSearchAllEmployeesByProcessingCountryComplete @SearchString,NULL,@MaxResults,@ProcessingCountry")
            End If

            Dim loCrypt As Encryption = New Encryption

            Dim lsFilter As String = psFilter

            Dim Crypt As New Objects.Security.Cryptography.Encryption()
            With loComm
                loComm.Parameters.Add(New SqlParameter("@SearchString", psSearchString.Replace("'", "''")))
                If psFilter.Trim.Length > 0 Then
                    loComm.Parameters.Add(New SqlParameter("@Filter", psFilter))
                End If
                loComm.Parameters.Add(New SqlParameter("@MaxResults", piMaxResults))
                loComm.Parameters.Add(New SqlParameter("@ProcessingCountry", psProcessingCountry.Replace("'", "''")))

            End With
            Dim loAdp As SqlDataAdapter = New SqlDataAdapter(loComm)
            Dim loDT As DataTable = New DataTable
            loAdp.Fill(loDT)
            loComm.Connection.Close()
            Return loDT
        End Using
    End Function

    ''' <summary>
    ''' 
    ''' Returns employees from GSS deployed Countries
    ''' </summary>
    ''' <param name="psSearchString"></param>
    ''' <param name="psFilter"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function EmployeeSearchGSS(ByVal psSearchString As String, ByVal psFilter As String) As DataTable
        Dim loConn As ConnectionServer.Connection = New ConnectionServer.Connection(ConnectionServer.Connection.SourceDataBase.CMT)
        Dim loComm As SqlCommand

        psSearchString = psSearchString.Replace(", ", ",")
        psSearchString = psSearchString.Trim

        If psFilter.Trim.Length > 0 Then
            loComm = loConn.Command("EXEC dbo.EmployeeSearchGSSCountries @SearchString,@Filter")
        Else
            loComm = loConn.Command("EXEC dbo.EmployeeSearchGSSCountries @SearchString,NULL")
        End If

        Dim loCrypt As Encryption = New Encryption

        Dim lsFilter As String = psFilter

        Dim Crypt As New Objects.Security.Cryptography.Encryption()
        With loComm
            loComm.Parameters.Add(New SqlParameter("@SearchString", psSearchString.Replace("'", "''")))
            If psFilter.Trim.Length > 0 Then
                loComm.Parameters.Add(New SqlParameter("@Filter", psFilter))
            End If

        End With
        Dim loAdp As SqlDataAdapter = New SqlDataAdapter(loComm)
        Dim loDT As DataTable = New DataTable
        loAdp.Fill(loDT)
        loComm.Connection.Close()
        Return loDT

    End Function


    ''' <summary>
    ''' Function: GetOrgChart
    ''' </summary>
    ''' <param name="IsSupport"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetOrgChart(ByVal IsSupport As Boolean) As DataSet
        Dim loConnServer As ConnectionServer.Connection = New ConnectionServer.Connection

        Using loConn As SqlClient.SqlConnection = CType(loConnServer.GetConnection(ConnectionServer.Connection.SourceDataBase.CMT), SqlConnection)


            Dim loComm As SqlCommand
            If IsSupport Then
                loComm = New SqlCommand("EXEC dbo.USER_GetOrgChart_OpsSupport", loConn)
            Else
                loComm = New SqlCommand("EXEC dbo.USER_GetOrgChart_Ops", loConn)
            End If

            Dim loDataSet As DataSet = New DataSet
            Dim loTable As DataTable
            Dim llTableCount As Integer
            Dim loRelation As DataRelation
            Dim loRow As DataRow
            Dim loRawTable As DataTable = New DataTable
            Dim loTableMaster As DataTable

            Dim loAdp As SqlDataAdapter = New SqlDataAdapter(loComm)
            Try
                loAdp.Fill(loRawTable)
            Finally
                loConn.Close()
            End Try
            For Each ldrRaw As DataRow In loRawTable.Rows

                If loDataSet.Tables.IndexOf(ldrRaw("Parent").ToString) = -1 Then
                    Dim loNewTable As DataTable = New DataTable(ldrRaw("Parent").ToString)
                    loNewTable.Columns.AddRange(New DataColumn() _
                            {New DataColumn("Child"), New DataColumn("Parent"), _
                            New DataColumn("CommonName"), New DataColumn("Description"), _
                            New DataColumn("Status"), New DataColumn("MSort")})
                    loDataSet.Tables.Add(loNewTable)
                End If
                loTable = loDataSet.Tables(ldrRaw("Parent").ToString)
                If Not loTable Is Nothing Then
                    Dim ldrNew As DataRow = loTable.NewRow
                    ldrNew.ItemArray = ldrRaw.ItemArray
                    loTable.Rows.Add(ldrNew)
                End If

            Next

            For Each loTableMaster In loDataSet.Tables
                loTableMaster.TableName = llTableCount.ToString
                llTableCount += 1
                For Each loRow In loTableMaster.Rows
                    For Each loTable In loDataSet.Tables
                        If loTable.TableName <> loTableMaster.TableName Then
                            If loTable.Rows.Count > 0 Then
                                If Not IsDBNull(loTable.Rows(0)("Parent")) Then
                                    If loRow("Child").ToString = loTable.Rows(0)("Parent").ToString Then
                                        loRelation = New DataRelation(loRow("CommonName").ToString, loTableMaster.Columns("Child"), loTable.Columns("Parent"))
                                        Try
                                            loDataSet.Relations.Add(loRelation)
                                        Catch ex As Exception
                                        End Try
                                    End If
                                End If
                            End If
                        End If
                    Next
                Next
            Next
            Return loDataSet
        End Using
    End Function

    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' Returns a relational Dataset outlining the reporting structure
    ''' </summary>
    ''' <param name="psReportTo">The top empployee's boss</param>
    ''' <param name="pbIsLeadership">Limit results to Leadership only</param>
    ''' <returns></returns>
    ''' <remarks>
    ''' </remarks>    
    ''' -----------------------------------------------------------------------------
    Public Function GetOrgChart(ByVal psReportTo As String, ByVal pbIsLeadership As Boolean) As DataSet

        Dim loConnServer As ConnectionServer.Connection = New ConnectionServer.Connection
        Dim loConn As SqlConnection = loConnServer.GetConnection(Connection.SourceDataBase.CMT)
        Dim loComm As SqlCommand = New SqlCommand("EXEC dbo.USER_GetOrgChart " & psReportTo & "," & CInt(pbIsLeadership), loConn)
        Dim ladpOrgChart As SqlDataAdapter = New SqlDataAdapter(loComm)
        Dim ldsOrgChart As DataSet = New DataSet
        Dim loTableMaster As DataTable
        Dim loTable As DataTable
        Dim liTableCount As Integer
        Dim loRelation As DataRelation
        Dim loRow As DataRow
        Try
            ladpOrgChart.Fill(ldsOrgChart)
        Finally
            loConn.Close()
        End Try
        For Each loTableMaster In ldsOrgChart.Tables
            loTableMaster.TableName = liTableCount.ToString
            liTableCount += 1
            For Each loRow In loTableMaster.Rows
                For Each loTable In ldsOrgChart.Tables
                    If loTable.TableName <> loTableMaster.TableName Then
                        If loTable.Rows.Count > 0 Then
                            If Not IsDBNull(loTable.Rows(0)("Parent")) Then
                                If loRow("Child").ToString = loTable.Rows(0)("Parent").ToString Then
                                    loRelation = New DataRelation(loRow("CommonName").ToString, loTableMaster.Columns("Child"), loTable.Columns("Parent"))
                                    ldsOrgChart.Relations.Add(loRelation)
                                End If
                            End If
                        End If
                    End If
                Next
            Next
        Next
        Return ldsOrgChart
    End Function

End Class
